package crawler.src;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class DB {
	
	private static final String USER ="browler";
	private static final String PASS = "passwordProtected";
	
	private String url = "jdbc:mysql://localhost/Crawler";
	
	private Statement stmt = null;
	private Connection conn = null;
	
	public DB() {
		try{
		      Class.forName("com.mysql.jdbc.Driver");
		      
		      conn = DriverManager.getConnection
		  			("jdbc:mysql://localhost/", "root", "");
		      
		      stmt = conn.createStatement();
		      stmt.execute("GRANT ALL PRIVILEGES ON crawler.* TO '"+ USER +"'@'localhost' IDENTIFIED BY '"+ PASS +"' WITH GRANT OPTION;");
		      stmt.close();
		      
		      conn = DriverManager.getConnection
			  			("jdbc:mysql://localhost/", USER, PASS);
		      stmt = conn.createStatement();
		      
		      

		      stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS crawler");
		      stmt.executeUpdate("USE crawler;");
		      
		      String sql = "CREATE TABLE IF NOT EXISTS RECORD " +
		                   "(recordid int NOT NULL AUTO_INCREMENT, " +
		                   " url text, "
		                   + "PRIMARY KEY(recordid))"; 

		      stmt.executeUpdate(sql);
		      
		      sql = "CREATE TABLE IF NOT EXISTS KEYWORDS " +
	                   "(keywordid int NOT NULL AUTO_INCREMENT, " +
	                   " word VARCHAR(255), "
	                   + "PRIMARY KEY(keywordid))";
		      
		      stmt.executeUpdate(sql);
		      
		      sql = "CREATE TABLE IF NOT EXISTS BLOCKED " +
	                   "(blockedid INTEGER NOT NULL AUTO_INCREMENT, " +
	                   " url text NOT NULL, "
	                   + "word text NOT NULL, "
	                   + "PRIMARY KEY(blockedid)) ";
		      
		      stmt.executeUpdate(sql);
		      
		      sql = "CREATE TABLE IF NOT EXISTS PASSWORD"
		      		+ "(pass VARCHAR(255) NOT NULL, "
		      		+ "PRIMARY KEY(pass))";
		      
		      stmt.executeUpdate(sql);
		      
		   }catch(SQLException se){
		      se.printStackTrace();
		   }catch(Exception e){
		      e.printStackTrace();
		   }
	}
	
	public void cleanDB() {
		String query = "TRUNCATE KEYWORDS";
		
		try {
			conn = DriverManager.getConnection ("jdbc:mysql://localhost/?user=root&password=");
			stmt = conn.createStatement();
		    stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS crawler");
		    stmt.executeUpdate("USE crawler;");
		    stmt.executeUpdate(query);
		    query = "TRUNCATE RECORD";
		    stmt.executeUpdate(query);
		    query = "TRUNCATE BLOCKED";
		    stmt.executeUpdate(query);
		    query = "TRUNCATE PASSWORD";
		    stmt.executeUpdate(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public Connection getConnection() {
		return conn;
	}
 
	public ResultSet runSql(String sql) throws SQLException {
		Statement sta = conn.createStatement();
		return sta.executeQuery(sql);
	}
 
	public boolean runSql2(String sql) throws SQLException {
		Statement sta = conn.createStatement();
		return sta.execute(sql);
	}
 
	@Override
	protected void finalize() throws Throwable {
		if (conn != null || !conn.isClosed()) {
			conn.close();
		}
	}
}